do "E:\yungyu\lottery_baby\do\set_environment.do"

forv i = 96(1)109{
	loc j = `i'+1
	dis "Year `i'..."
	qui{
		noi dis "-- Clean JHQ"
		use "$public/observation/real_jhq_v2.dta", clear
		gen year = `i'
		gen age = year - born_year
		keep if inrange(age,19,75) & death_year > `j'
		
		keep year idn gender age
		keep if gender == 1 | gender == 2
		duplicates drop idn, force
		
		noi dis "-- Merge Income & Earnings of This Year"
		merge 1:1 year idn using "$wdata/income_`i'.dta" , update keepusing(income_iimhnolottery)
		drop if _m == 2
		drop _m
		recode income_iimhnolottery . = 0
		rename income_iimhnolottery income0
		merge 1:1 year idn using "$earnings/earnings_`i'.dta", update keepusing(earnings)
		drop if _m == 2
		drop _m
		recode earnings . = 0
		rename earnings earnings0
		merge m:1 year using "$wdata/cpi.dta", keepusing(cpi)
		drop if _m == 2
		drop _m
		replace income0 = income0/cpi*100
		replace earnings0 = earnings0/cpi*100
		drop cpi
		
		noi dis "-- Merge Income & Earnings of Next Year"
		replace year = `j'
		merge 1:1 year idn using "$wdata/income_`j'.dta" , update keepusing(income_iimhnolottery)
		drop if _m == 2
		drop _m
		recode income_iimhnolottery . = 0
		rename income_iimhnolottery income1
		merge 1:1 year idn using "$earnings/earnings_`j'.dta", update keepusing(earnings)
		drop if _m == 2
		drop _m
		recode earnings . = 0
		rename earnings earnings1
		merge m:1 year using "$wdata/cpi.dta", keepusing(cpi)
		drop if _m == 2
		drop _m
		replace income1 = income1/cpi*100
		replace earnings1 = earnings1/cpi*100
		drop cpi
		
		noi dis "-- Calculate Growth Rate"
		gen income_gr = (income1-income0)/income0
		gen earnings_gr = (earnings1-earnings0)/earnings0
		
		noi dis "-- Calculate Average"
		replace year = `i'
		collapse 	(mean)mean_income0 = income0 ///
					(median)median_income0 = income0 ///
					(mean)mean_income1 = income1 ///
					(median)median_income1 = income1 ///
					(mean)mean_income_gr = income_gr ///
					(median)median_income_gr = income_gr ///
					(mean)mean_earnings0 = earnings0 ///
					(median)median_earnings0 = earnings0 ///
					(mean)mean_earnings1 = earnings1 ///
					(median)median_earnings1 = earnings1 ///
					(mean)mean_earnings_gr = earnings_gr ///
					(median)median_earnings_gr = earnings_gr ///
					, by(year age gender)

		noi dis "-- Save Data"
		save "$wdata/earnings_growthrate_`i'.dta", replace
	}
}

clear
forv i = 96(1)109{
	ap using "$wdata/earnings_growthrate_`i'.dta"
	collapse (mean)mean* median*, by(age gender)
}
save "$wdata/earnings_growthrate.dta", replace
